using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public BookclifotoVO getBookclifoto(int? idBookCliFoto)
		{
			BookclifotoVO bookclifotoVO = null;
			List<BookclifotoVO> listBookclifotoVOs = getBookclifotos(idBookCliFoto, null);
			if (listBookclifotoVOs != null && listBookclifotoVOs.Count > 0)
			{
				bookclifotoVO = listBookclifotoVOs[0];
			}
			return bookclifotoVO;
		}

		public List<BookclifotoVO> getBookclifotos()
		{
			return getBookclifotos(null, null);
		}

		public List<BookclifotoVO> getBookclifotos(int? idBookCliFoto)
		{
			return getBookclifotos(idBookCliFoto, null);
		}

		public List<BookclifotoVO> getBookclifotos(int? idBookCliFoto, int? idBookCli)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT bcf.idBookCliFoto as bcf_idBookCliFoto, bcf.idBookCli as bcf_idBookCli, bcf.fotoMime as bcf_fotoMime, bcf.fotoNome as bcf_fotoNome , bcf.destaque as bcf_destaque, bcf.cadastro as bcf_cadastro, bcf.cadastradoPor as bcf_cadastradoPor, bcf.edicao as bcf_edicao, bcf.editadoPor as bcf_editadoPor  ");
			query.AppendLine("FROM bookclifoto as bcf LEFT OUTER JOIN bookcli as bcl on bcf.idBookCli=bcl.idBookCli  ");
			if (idBookCliFoto != null)
			{
				query.AppendLine(where + "bcf.idBookCliFoto=@idBookCliFoto ");
				where = "AND ";
			}
			if (idBookCli != null)
			{
				query.AppendLine(where + "bcf.idBookCli=@idBookCli ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY bcf.idBookCliFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idBookCliFoto!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookCliFoto", idBookCliFoto));
			if (idBookCli!= null) cmd.Parameters.Add(getParameter(cmd, "@idBookCli", idBookCli));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<BookclifotoVO> listBookclifoto = new List<BookclifotoVO>();

				while (reader.Read())
				{
					BookclifotoVO bookclifotoVO = BookclifotoVO.CreateVO();
					bookclifotoVO.idBookCliFoto = reader.GetInt32(reader.GetOrdinal("bcf_idBookCliFoto"));
					bookclifotoVO.idBookCli = reader.GetInt32(reader.GetOrdinal("bcf_idBookCli"));
					if (!reader.IsDBNull(reader.GetOrdinal("bcf_fotoMime")))
						bookclifotoVO.fotoMime = reader.GetString(reader.GetOrdinal("bcf_fotoMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("bcf_fotoNome")))
						bookclifotoVO.fotoNome = reader.GetString(reader.GetOrdinal("bcf_fotoNome"));
					bookclifotoVO.destaque = reader.GetString(reader.GetOrdinal("bcf_destaque")).Equals("S");
					bookclifotoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("bcf_cadastro"));
					bookclifotoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("bcf_cadastradoPor"));
					bookclifotoVO.edicao = reader.GetDateTime(reader.GetOrdinal("bcf_edicao"));
					bookclifotoVO.editadoPor = reader.GetString(reader.GetOrdinal("bcf_editadoPor"));


					listBookclifoto.Add(bookclifotoVO);
				}
				reader.Close();
				return listBookclifoto;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setBookclifoto(BookclifotoVO bookclifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE bookclifoto ");
			query.AppendLine("SET idBookCli=@idBookCli, fotoMime=@fotoMime, fotoNome=@fotoNome, destaque=@destaque,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idBookCliFoto=@idBookCliFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCliFoto", bookclifotoVO.idBookCliFoto));
			cmd.Parameters.Add(getParameter(cmd, "@idBookCli", bookclifotoVO.idBookCli));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", bookclifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", bookclifotoVO.fotoNome));
			cmd.Parameters.Add(getParameter(cmd, "@destaque", (bookclifotoVO.destaque==null?" ":(bookclifotoVO.destaque.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", bookclifotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", bookclifotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", bookclifotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", bookclifotoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newBookclifoto(BookclifotoVO bookclifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO bookclifoto ");
			query.AppendLine("  ( idBookCli, fotoMime, fotoNome, destaque, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @idBookCli, @fotoMime, @fotoNome, @destaque, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCli", bookclifotoVO.idBookCli));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", bookclifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", bookclifotoVO.fotoNome));
			cmd.Parameters.Add(getParameter(cmd, "@destaque", (bookclifotoVO.destaque==null?" ":(bookclifotoVO.destaque.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", bookclifotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", bookclifotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", bookclifotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", bookclifotoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('bookclifoto') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delBookclifoto(BookclifotoVO bookclifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM bookclifoto ");
			query.AppendLine("WHERE idBookCliFoto=@idBookCliFoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCliFoto", bookclifotoVO.idBookCliFoto));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountBookclifotoxIdBookCli(int? idBookCli)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idBookCli) as ocorrencias FROM bookclifoto WHERE IdBookCli=@IdBookCli ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdBookCli", idBookCli));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public void setBookclifotoFoto(BookclifotoVO bookclifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE bookclifoto ");
			query.AppendLine("SET foto=@foto, fotoMime=@fotoMime, fotoNome=@fotoNome ");
			query.AppendLine("WHERE idBookCliFoto=@idBookCliFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCliFoto", bookclifotoVO.idBookCliFoto));

			cmd.Parameters.Add(getParameter(cmd, "@foto", bookclifotoVO.foto));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMime", bookclifotoVO.fotoMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoNome", bookclifotoVO.fotoNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public void getBookclifotoFoto(BookclifotoVO bookclifotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT foto as bcf_foto, fotoMime as bcf_fotoMime, fotoNome as bcf_fotoNome ");
			query.AppendLine("FROM bookclifoto ");
			query.AppendLine("WHERE idBookCliFoto=@idBookCliFoto ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idBookCliFoto", bookclifotoVO.idBookCliFoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("bcf_foto")))
						bookclifotoVO.foto = getBytesFromBlob(reader, "bcf_foto");

					if (!reader.IsDBNull(reader.GetOrdinal("bcf_fotoMime")))
						bookclifotoVO.fotoMime = reader.GetString(reader.GetOrdinal("bcf_fotoMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("bcf_fotoNome")))
						bookclifotoVO.fotoNome = reader.GetString(reader.GetOrdinal("bcf_fotoNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

	}
}
